XplentyでAPIを叩いてデータを取得してみる（政府統計の総合窓口（e-Stat））

「カスタマーストーリー」を支えるツール特集

#ビジネス・アナリティクス

#ビッグデータ

#Xplenty

#Amazon Redshift

#AWS

kobayashi.m

2020.07.14

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

データアナリティクス事業本部のkobayashiです。

XplentyはETL、ELTツールとして様々なデータソースを扱え、また処理もXplentyのGUIで直感的に作成できます。今回もXplentyを使ってAPIを叩いてデータを取しそのデータをDBに保存してみます。

Xplenty | Simplified ETL & ELT to BigQuery, Snowflake, Redshift & Azure

前回まではレスポンスの取り扱いにそれほど困ることがないパターンを扱いました。今回は複雑なレスポンスを返すAPIのデータをXplentyで取り扱ってみたいと思い、e-StatのAPIをソースにし設定・ジョブ実行を行います。またトークンや統計表IDを変数に格納して汎用的に扱えるようにします。

前回の記事

APIで取得するデータ

今回は政府統計の総合窓口（e-Stat）のデータを取り扱います。 e-Statとは各省庁が実施している統計調査のデータを利用できるサイトです。

政府統計の総合窓口（ｅ－Ｓｔａｔ）は、各府省情報化統括責任者（CIO）連絡会議で決定された「統計調査等業務の業務・システム最適化計画」に基づき、日本の政府統計関係情報のワンストップサービスを実現するため2008年から本運用を開始した政府統計のポータルサイトです。

引用元このサイトについて | 政府統計の総合窓口

e-StatにはAPIがありユーザー登録をしてトークンを取得することでAPI経由でデータが取得できます。

政府統計の総合窓口(e-Stat)−API機能へようこそ | 政府統計の総合窓口(e-Stat)−API機能

今回はこの中の「平成２７年国勢調査小地域　年齢（５歳階級、４区分）別、男女別人口東京都（統計表ID：8003000051）」のデータをXplentyを使って取得し、最終的にRedshiftへ保存します。

e-stat APIのレスポンスの確認

今回扱うAPIは政府統計の総合窓口（e-Stat）のAPI 仕様 3.0版 | 政府統計の総合窓口(e-Stat)−API機能になります。Curl等でリクエストURLを叩いてレスポンスを確認します。

リクエストURL

http://api.e-stat.go.jp/rest/3.0/app/json/getStatsData?appId={トークン}d&lang=J&statsDataId=8003000051&metaGetFlg=Y&explanationGetFlg=N&annotationGetFlg-N&cntGetFlg=N

レスポンス

{
  "GET_STATS_DATA": {
    "RESULT": {
      "STATUS": 0,
      "ERROR_MSG": "正常に終了しました。",
      "DATE": "2020-06-24T10:34:26.720+09:00"
    },
    "PARAMETER": {
      "LANG": "J",
      "STATS_DATA_ID": 8003000051,
      "NARROWING_COND": {
        "LEVEL_AREA_COND": 3
      },
      "DATA_FORMAT": "J",
      "START_POSITION": 1,
      "LIMIT": 10,
      "METAGET_FLG": "Y",
      "EXPLANATION_GET_FLG": "N",
      "CNT_GET_FLG": "N"
    },
    "STATISTICAL_DATA": {
      ...
      },
      "CLASS_INF": {
        "CLASS_OBJ": [
          {
            "@id": "cat01",
            "@name": "年齢（５歳階級、４区分）別、男女別人口",
            "CLASS": [
              {
                "@code": "0010",
                "@name": "総数、年齢「不詳」含む",
                "@level": "1",
                "@unit": "人"
              },
              {
                "@code": "0020",
                "@name": "　総数０～４歳",
                "@level": "2",
                "@unit": "人",
                "@parentCode": "0010"
              },
              ...
            ]
          },
          {
            "@id": "cat02",
            "@name": "秘匿地域・合算地域有り",
            "CLASS": [
              {
                "@code": "1",
                "@name": "無し",
                "@level": "1"
              },
              {
                "@code": "2",
                "@name": "合算",
                "@level": "1"
              },
              {
                "@code": "3",
                "@name": "秘匿",
                "@level": "1"
              }
            ]
          },
          {
            "@id": "area",
            "@name": "東京都",
            "CLASS": [
              {
                "@code": "13101001001",
                "@name": "千代田区丸の内１丁目",
                "@level": "3",
                "@parentCode": "131010010"
              },
              {
                "@code": "13101001002",
                "@name": "千代田区丸の内２丁目",
                "@level": "3",
                "@parentCode": "131010010"
              },
              ...
            ]
          }
        ]
      },
      "DATA_INF": {
        "NOTE": {
          "@char": "-",
          "$": "当該数値がないもの"
        },
        "VALUE": [
          {
            "@cat01": "0010",
            "@cat02": "1",
            "@area": "13101005001",
            "@unit": "人",
            "$": "0"
          },
          {
            "@cat01": "0010",
            "@cat02": "1",
            "@area": "13101008001",
            "@unit": "人",
            "$": "832"
          },
          ...
        ]
      }
    }
  }
}

レスポンスを見ていくと $.GET_STATS_DATA.STATISTICAL_DATAの中に欲しいデータが入っており、さらにその中のCLASS_INF.CLASS_OBJにcat01,cat02,areaのマスタ類がリストで入っています。更にその中のCLASSにリストでマスタデータが入っています。

同じく$.GET_STATS_DATA.STATISTICAL_DATAの中にDATA_INFがありこの中のVALUEにリスト形式で統計データが入っています。

Xplentyのパッケージの作成

Xplentyのパッケージの中身は大まかに以下になります。

e-stat APIにリクエストを送りレスポンスを取得する
受け取ったデータをマスタ類と統計データに分ける
マスタデータが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
加工したマスタデータをマスタテーブル(estat_mst)に保存できるようにまとめる
統計データが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
マスタテーブル(estat_mst)と統計データテーブル(estat_data)用のRedshiftコンポーネントを作成する

これらのコンポーネントを組み合わせてパッケージを作成していきます。

はじめにサイドバーからPackagesを選択してNew Packageを押下し新しいパッケージを作成します。

1.e-stat API用にREST APIコンポーネントを作成

まずはe-statのAPIを使ってデータを取得し、加工・保存を行います。またトークンが変わった際の利便性や統計表IDを簡単に切り替えて他のデータを取得しやすくするため、Xplentyのユーザー変数機能を使います。

手順1-1）.画面上部の...を押下し編集設定画面へ進む。

手順1-2）.Add anotherを押下してトークン用、統計表ID用の変数を設定しSaveを押下して保存する。

key	value
appId	'{トークン}'
statsDataId	'8003000051'

文字列として扱うので値は必ず'クォートで囲います。ここで変数を指定することで以降の手順中にて$appId,$statsDataIdの形で設定した値を取り出せます。

手順1-3）.Add componetを押下し、SourcesでREST APIを選択する

手順1-4）.作成されたアイコンをダブルクリックするとREST APIコンポーネント設定用のモーダルが表示されるので、AuthenticationでNoneを選択する。

手順1-5）.Request and responseの設定を行いNextを押下する。

Method : GETを選択
URL : http://api.e-stat.go.jp/rest/3.0/app/json/getStatsData?appId=$appId&lang=J&statsDataId=$statsDataId&metaGetFlg=Y&explanationGetFlg=N&annotationGetFlg-N&cntGetFlg=Nを入力
Headers
- Key : Content-Typeを入力
- VALUE : application/jsonを入力
Body : (空)
Use Pagination : チェックしない
Base record JSONPath Expression : $.GET_STATS_DATA.STATISTICAL_DATAを入力

他の設定値はデフォルトのままにします。

手順1-6）.Response Schemaへ進み、Selected Fieldsで右下のAddを押下し下記の設定値でSelected Fieldsを設定する。その後Saveを押下し、設定を保存する。

Key	Alias	Type
CLASS_INF	CLASS_INF	MAP
DATA_INF	DATA_INF	MAP

この画面ではレスポンスの内容を確認できるので必要なデータを確認しながら設定を行えます。

これでREST APIコンポーネントの設定は終わりますので、次にデータ加工用のコンポーネントをを設定します。

2. 受け取ったデータをマスタ類と統計データに分ける

手順2-1）.Add componetを押下し、TransformationsでCloneを選択してCloneコンポーネントを作成しREST APIコンポーネントと接続する

3. マスタデータが入ったJsonデータを加工してRedshiftへ保存できる形に加工する

次にマスタデータを作成します。Selectコンポーネントを2つ繋げて、1つ目のコンポーネントでレスポンスをフラット化し、2つ目のコンポーネントでJson文字列を扱う関数を駆使して欲しい形に加工します。これをcat01,cat02,areaの3種類分設定します。

手順3-1）.Add componetを押下し、TransformationsでSelectを選択する。この操作を2回行い2つSelectコンポーネントを作成する。

手順3-2）.作成したSelectコンポーネントを前段のREST APIコンポーネントと接続する。（接続していないとカラムのマッピングができないため）

手順3-3）.作成したSelectコンポーネントの1つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
JsonStringToMap(JsonExtract (CLASS_INF#'CLASS_OBJ','$.[0]'))#'@id'	class_id
JsonStringToMap(JsonExtract (CLASS_INF#'CLASS_OBJ','$.[0]'))#'@name'	class_name
Flatten(JsonStringToBag (JsonExtract (CLASS_INF#'CLASS_OBJ','$.[0].CLASS')))	values
ToString(SwitchTimeZone(CurrentTime(),'Asia/Tokyo'), 'yyyy-MM-dd\'T\'HH:mm:ss')	created_datetime

手順3-4）.作成したSelectコンポーネントの2つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
class_id	class_id
class_name	class_name
JsonStringToMap(values)#'@code'	code
JsonStringToMap(values)#'@parentCode'	parentCode
JsonStringToMap(values)#'@unit'	unit
JsonStringToMap(values)#'@level'	level
JsonStringToMap(values)#'@name'	name
created_datetime	created_datetime

手順3-1〜3-4を繰り返して3種類分作成します。

作成する際の手順時の変更点は1箇所です。

cat02の作成をする際には手順3-3）でのEXPRESSIONの設定で'$.[0]'の部分を'$.[1]'に変更
areaの作成をする際には手順3-3）でのEXPRESSIONの設定で'$.[0]'の部分を'$.[2]'に変更

設定が終わると下記の状態になります。

4. 加工したマスタデータをマスタテーブルに保存できるようにまとめる

cat01,cat02,areaで別テーブル分けるよりも1テーブルでまとめたほうが後々の扱いが楽なのでマスタテーブルとして1テーブルに保存します。また他の統計表IDのデータを入れることも考えstatsDataIdカラムも作成します。

手順4-1）.Add componetを押下し、TransformationsでUnionを選択し前段のコンポーネント2つ(cat01,cat02用)を接続する。

手順4-2）.Unionコンポーネントをダブルクリックし設定画面に進み、Auto fillを押下して自動設定でカラムマッピングを行う。

手順4-1、4-2を繰り返し、手順4-2）のUnionコンポーネントとareaのSelectコンポーネントを結合します。

手順4-3）.cat01,cat02,areaを結合したUnionコンポーネントに新規に作成したSelectコンポーネントを接続する。

手順4-4）.Selectコンポーネントをダブルクリックし、カラムマッピングを行う。統計表IDを追加したいのでAuto fillを押下して自動設定を行った後に以下を追加する。

EXPRESSION : '$statsDataId'を入力(文字列として扱うので値は必ず'クォートで囲む)
ALIAS : statsDataIdを入力

ここまでの手順で下図の形になります。

5. 統計データが入ったJsonデータを加工してRedshiftへ保存できる形に加工する

マスタデータの作成は終わったので次に統計データを作成します。ここは再度Json文字列からデータベース用のデータを作成するので、Selectコンポーネントを2つ繋げて、1つ目のコンポーネントでレスポンスをフラット化し、2つ目のコンポーネントでJson文字列を扱う関数を駆使して欲しい形に加工します。

手順5-1）.Add componetを押下し、TransformationsでSelectを選択する。この操作を2回行い2つSelectコンポーネントを作成する。

手順5-2）.作成したSelectコンポーネントを前段のCloneコンポーネントと接続する。（接続していないとカラムのマッピングができないため）

手順3-3）.作成したSelectコンポーネントの1つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
Flatten(JsonStringToBag (DATA_INF#'VALUE'))	values
ToString(SwitchTimeZone(CurrentTime(),'Asia/Tokyo'), 'yyyy-MM-dd\'T\'HH:mm:ss')	created_datetime

手順3-4）.作成したSelectコンポーネントの2つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
'$statsDataId'	statsDataId
JsonStringToMap(values)#'@cat01'	cat01
JsonStringToMap(values)#'@cat02'	cat02
JsonStringToMap(values)#'@area'	area
JsonStringToMap(values)#'@unit'	unit
JsonStringToMap(values)#'$'	cnt
created_datetime	created_datetime

6. 保存先としてRedshiftコンポーネント作成

前回までと同様に簡単に行うためテーブルは予め作成せずXplentyのジョブで自動作成し、データは毎回洗い替えを行う設定にします。

手順4-1）.Add componetを押下し、DestinationsでAmazon Redshiftを選択する

手順4-2）.画面上にRedshiftのアイコンが作成されるので先に作成したSelectコンポーネントのアイコンとDrag&Dropで紐付ける。

手順4-3）.RedshiftのアイコンをダブルクリックするとRedshiftコンポーネント設定用のモーダルが表示されるので、設定済みのRedshiftのconnectionを選択する。

手順4-4）.移行先のRedshiftのスキーマとテーブルを入力し各種設定を行い、Nextを押下する。

Automatically create table if it doesn't exist : チェックする
Automatically add missing columns : チェックする
Operation type : Overwrite (Truncate and insert)を選択する

他の設定はデフォルトで問題ありません。

これでパッケージの作成は完了です。最終的なパッケージの形は下図になります。

Xplentyのパッケージの実行と確認

実際にジョブを実行して結果を確認します。

Redshitでデータを確認

マスタテーブル

select * from cm_kobayashi_test.estat_mst;

statsdataid	class_id	class_name	code	parentcode	unit	level	name	created_datetime
8003000051	cat01	年齢（５歳階級、４区分）別、男女別人口	0010	NULL	人	1	総数、年齢「不詳」含む	2020-06-29T09:55:27
8003000051	cat02	秘匿地域・合算地域有り	2	NULL	NULL	1	合算	2020-06-29T09:55:27
8003000051	area	東京都	13101002001	131010020	NULL	3	千代田区大手町１丁目	2020-06-29T09:55:27
8003000051	area	東京都	13101004001	131010040	NULL	3	千代田区有楽町１丁目	2020-06-29T09:55:27

Redshitでデータを確認

統計データテーブル

select * from cm_kobayashi_test.estat_data;

statsdataid	cat01	cat02	area	unit	cnt	created_datetime
8003000051	0010	1	13101008002	人	933	2020-06-29T09:55:27
8003000051	0010	1	13101009004	人	605	2020-06-29T09:55:27
8003000051	0010	1	13101016002	人	820	2020-06-29T09:55:27
8003000051	0010	1	13101017002	人	266	2020-06-29T09:55:27
8003000051	0010	1	13101018002	人	3199	2020-06-29T09:55:27